package jnpf.database.sql.impl;

import jnpf.database.constant.DbAliasConst;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.DbSttEnum;
import jnpf.database.model.DbFieldMod;
import jnpf.database.source.DbBase;
import jnpf.database.sql.SqlBase;
import jnpf.database.sql.append.insert.InsertSql;
import jnpf.database.sql.append.insert.InsertSqlDTO;
import jnpf.util.StringUtil;

import java.util.List;

/**
 * MySQL SQL语句模板
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/10/6
 */
public class SqlMySQL extends SqlBase {

    @Override
    protected void setSqlBase() {
        fieldListSql = "SELECT COLUMN_NAME " + DbAliasEnum.FIELD_NAME.AS() + ",data_type " + DbAliasEnum.DATA_TYPE.AS() +
                ",CHARACTER_MAXIMUM_LENGTH " + DbAliasEnum.DATA_LENGTH.AS() + ", " +
                "NUMERIC_PRECISION AS " + DbAliasConst.PRECISION + ",NUMERIC_SCALE AS " + DbAliasConst.DECIMALS + ", " +
                "IF ( IS_NULLABLE = 'YES', '1', '0' ) " + DbAliasEnum.ALLOW_NULL.AS() + ", COLUMN_COMMENT " + DbAliasEnum.FIELD_COMMENT.AS() + "," +
                "IF ( COLUMN_KEY = 'PRI', '1', '0' ) " + DbAliasEnum.PRIMARY_KEY.AS() + ", " +
                "column_default " + DbAliasEnum.DEFAULTS.AS() + "," +
                "CONCAT(upper(COLUMN_NAME),'(',COLUMN_COMMENT,')') as 'F_DESCRIPTION' " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_NAME = '" + DbSttEnum.TABLE.getTarget() + "'AND TABLE_SCHEMA='" + DbSttEnum.DB_NAME.getTarget() + "'";
        tableListSql = "SELECT table_name " + DbAliasEnum.TABLE_NAME.AS() + ",table_rows " + DbAliasEnum.TABLE_SUM.AS() + "," +
                " data_length " + DbAliasEnum.TABLE_SIZE.AS() + ", table_comment " + DbAliasEnum.TABLE_COMMENT.AS() + ", " +
                "CONCAT(table_name,'(',table_comment,')') as 'F_DESCRIPTION' FROM information_schema.TABLES WHERE " +
                "TABLE_SCHEMA = '" + DbSttEnum.DB_NAME.getTarget() + "'";
    }

    public SqlMySQL(DbBase dbBase) {
        super(dbBase);
    }

    @Override
    public String batchInsertSql(List<List<DbFieldMod>> dataList, String table) {
        InsertSqlDTO iInsertSqlDTO = new InsertSqlDTO(this.dbBase, table, dataList, ",");
        String sql = InsertSql.batch(iInsertSqlDTO);
        //去除最后一个逗号
        return sql.substring(0, sql.length() - 1) + ";";
    }

    @Override
    public String[] getPageSql(String sql, String sortType, Integer currentPage, Integer pageSize) {
        /*mysql可以用 SELECT SQL_CALC_FOUND_ROWS * FROM table
                LIMIT index;SELECT FOUND_ROWS();方法获得两个结果集*/
        int startIndex = currentPage - 1;
        // 获取dataListSql
        String dataListSql = sql + " ORDER BY " + sortType + " LIMIT " + startIndex * pageSize + "," + pageSize + ";";
        // 获取totalSql
        String totalSql = "SELECT COUNT(*) " + DbAliasEnum.TOTAL_RECORD.asByDb(this.dbBase) + " FROM (" + sql + ") jnpf_tab;";
        return new String[]{dataListSql, totalSql};
    }

    private String getTotalSql(String sql) {
        // SELECT COUNT(*) FROM  (SELECT * FROM base_dblink) jnfp_tab
        // 第一SELECT一定要大写，第一个FROM与SELECT之间，能插入COUNT(*),不报错
        int selectStar = sql.indexOf("SELECT");
        int fromEnd = sql.indexOf("FROM");
        return sql.substring(0, selectStar + 6) + " COUNT(*) AS totalRecord " + sql.substring(fromEnd);
    }

    /*==================特有的一些方法======================*/

    /**
     * 获取添加表时的字段注释
     *
     * @param fieldComment 字段注释
     * @return SQL片段
     */
    public static String getCreFieldComment(String fieldComment) {
        String mysqlFieldComment = "";
        //判断是否是Mysql类型
        if (StringUtil.isNotNull(fieldComment)) {
            mysqlFieldComment = "\tCOMMENT\t\'" + fieldComment + "\'";
        }
        return mysqlFieldComment;
    }

    /**
     * 获取注释
     * mysql独有注释方式
     *
     * @param tableComment 表注释
     * @return SQL片段
     */
    public static String getTabComment(String tableComment) {
        return "\tCOMMENT\t\'" + tableComment + "\';";
    }

}
